﻿package dut.ir.lxb.weibo.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dut.ir.lxb.weibo.bean.EventBean;

public class EventInfoDao
{
	private static EventInfoDao ud;
	
	private EventInfoDao()
	{
		
	}
	
	public static EventInfoDao getInstance()
	{
		if(ud == null)
		{
			ud = new EventInfoDao();
			return ud;
		}
		else
			return ud;
	}
	
	
	public EventBean getEventByName(String eventName)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select eventname,eventcode,url,hostperson,type,description from T_EventInfo where eventname=?";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, eventName);
			
			ResultSet rs = ps.executeQuery();
			EventBean eb = new EventBean();
			if(rs.next())
			{
				eb.setEventname(rs.getString(1));
				eb.setEventCode(rs.getString(2));
				eb.setUrl(rs.getString(3));
				eb.setHostPerson(rs.getString(4));
				eb.setType(rs.getString(5));
				eb.setDescription(rs.getString(6));
			}
			rs.close();
			ps.close();
			return eb;
			
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} 
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		
	}
	
	public List<EventBean> getAllEventList()
	{
		List<EventBean> eventList = new ArrayList<EventBean>();
		
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "select eventname,eventcode,url,hostperson,type,description from T_EventInfo";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				EventBean eb = new EventBean();
				eb.setEventname(rs.getString(1));
				eb.setEventCode(rs.getString(2));
				eb.setUrl(rs.getString(3));
				eb.setHostPerson(rs.getString(4));
				eb.setType(rs.getString(5));
				eb.setDescription(rs.getString(6));
				eventList.add(eb);
			}
			rs.close();
			ps.close();
			
			return eventList;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} 
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
	}
	
	
	public boolean hasRecord(EventBean eb)
	{
		if(eb == null)
			return false;
		EventBean newEvent = getEventByName(eb.getEventname());
		return newEvent == null ? false : true;
	}
	
	
	public boolean deleteEvent(String eventName)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "delete from T_EventInfo where eventname=?";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, eventName);
			
			boolean flag = ps.execute();
			ps.close();
			return flag;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
	}
	
	
	public boolean insertEventInfo(EventBean eb)
	{
		if(eb == null)
			return true;
		if(hasRecord(eb))
			deleteEvent(eb.getEventname());
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "insert into T_EventInfo (eventname,eventcode,url,hostperson,type,description) values(?,?,?,?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, eb.getEventname());
			ps.setString(2, eb.getEventCode());
			ps.setString(3, eb.getUrl());
			ps.setString(4, eb.getHostPerson());
			ps.setString(5, eb.getType());
			ps.setString(6, eb.getDescription());
			
			boolean flag = ps.execute();
			ps.close();
			return flag;
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} 
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
	}
	
	
	/**
	 * 获得每个时间段事件的关注度
	 * @param dateTime 时间，精确到天，格式形如2014-11-11
	 * @return 事件及事件关注度列表
	 */
	public List<EventBean> getEventListGroupbyTime(String dateTime)
	{
		DBConnectionManager connectionMan = DBConnectionManager.getInstance();
		Connection conn = null;
		try
		{
			conn = connectionMan.getConnection();
			String sqlStr = "SELECT eventname,count(distinct mid) as focuscount FROM DUTIRWeiboPlatform.T_WeiboInfo where to_days(?)=to_days(posttime) and eventname is not null and eventname <> ''  group by eventname order by focuscount desc;";
			PreparedStatement ps = conn.prepareStatement(sqlStr);
			
			//填充参数
			ps.setString(1, dateTime);
			
			List<EventBean> eventList = new ArrayList<EventBean>();
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				EventBean eb = new EventBean();
				eb.setEventname(rs.getString(1));
				eb.setFocusCount(rs.getInt(2));
				
				eventList.add(eb);
			}
			rs.close();
			ps.close();
			return eventList;
			
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			connectionMan.freeConnection(conn);
		}
		
		
	}
	
	
	
	
}
